<?php

/**
 * page class. Provide utilities functions related to page table in database.
 * <u>Note:</u> all $db param in first parameter is ezSQL_mysql object.
 * See ezSQL documentation for more information.
 */
class page 
{

    function page() 
    {
    }
    
    /**
     * Get latest added page.
     * @param $number the number page will be return
     * @return The list of page object. 
     * page object contain fields is same as defined in database  
     */
    function getLastestPage($db, $number)
    {
		$locale = $_SESSION["LC_ALL"];
    	$sql = sprintf("SELECT * FROM ".TBL_PAGE." p ".
						"LEFT JOIN ".TBL_PAGE_LANG." pl ON p.page_id = pl.page_id AND pl.lang_id = '$locale' ".	
						"ORDER BY p.page_modified desc LIMIT $number", 
    			TBL_PAGE);
    	return $db->get_results($sql);
    }
    
    /**
     * Get all page by inputted condition.
     * @param $cond the condition in SQL string. Ex: (id='1' or title='test')
     * @return The list of page object. 
     * page object contain fields is same as defined in database  
     */
    function getAllPageByCondition($db, $cond)
    {
    	$locale = $_SESSION["LC_ALL"];
    	
    	$sql = sprintf("SELECT * FROM %1\$s left join %2\$s " .
    			"on %1\$s.page_author = %2\$s.ID ".
				"LEFT JOIN %3\$s ON %1\$s.page_id = %3\$s.page_id AND %3\$s.lang_id = '$locale' ".	
				"WHERE %3\$s.lang_id='$locale' AND $cond", 
    			TBL_PAGE,
    			TBL_USER,
				TBL_PAGE_LANG);
    			
    	$res = $db->get_results($sql);
    	
    	return $res;
    }
    
    /**
     * Get all page in database.
     * @param $keyword used to filter page by checking page_name.
     * @return The list of page object.
     * If $keyword not null, function will return pages that
     * contain page_name in which $keyword contain in.
     * page object contain fields is same as defined in database.
     */
    function getAllPage($db, $keyword)
    {
    	$cond = "";
    	if($keyword != "")
    	{
    		$keyword = str_replace("%", "%%", $keyword);
    		$cond = " AND " . TBL_PAGE . ".page_name like '%%$keyword%%'";
    	}
    	
    	$locale = $_SESSION["LC_ALL"];
    	
    	$sql = sprintf("SELECT * FROM %1\$s left join %2\$s " .
    			"on %1\$s.page_author = %2\$s.ID ".
				"LEFT JOIN %3\$s ON %1\$s.page_id = %3\$s.page_id AND %3\$s.lang_id = '$locale' ".
				"where %3\$s.lang_id='$locale' $cond ", 
    			TBL_PAGE,
    			TBL_USER,
				TBL_PAGE_LANG);
    			
    	$res = $db->get_results($sql);
    	if($res!=""){
				foreach($res as $item){
					$item->page_name = html_entity_decode($item->page_name);
					$item->short_desciption = html_entity_decode($item->short_desciption);
					$item->page_content = html_entity_decode($item->page_content);
				}
			}
    	
    	return $res;
    }
    
    function getPageIdFromPage2Cat($db, $cat)
    {
    	$sql = "";
    	if($cat == "0")
		{
			$sql = 'select page_id from ' . TBL_PAGE2CAT;
		}
		else
		{	
			$sql = sprintf("select page_id from %s WHERE category_id={$cat}", TBL_PAGE2CAT);
		}
		
    	$res = $db->get_results($sql);
    	if($res != null)
    	{
    		$s = '';
    		foreach($res as $obj)
    		{
    			$s .= (empty($s)?'':',') . "'$obj->page_id'";
    		}
    		
    		return $s;
    	}
    	return  '';
    }
    
     /**
     * Get all page by category and userid
     * @param $cat the category id that page related to.
     * @param $userid the  user id that post page.
     * @return The list of page object.
     * page object contain fields is same as defined in database.
     */
    function getAllPageByCat($db, $cat, $userid)
    {
    	
    	$cond = "";
    	
    	if($cat != "")
    	{
    		$p2c = page::getPageIdFromPage2Cat($db, $cat);
    		
    		if($p2c == '') return null;
    		
    		//If Category Id = 0 we reverd SQL
    		if($cat == "0")
    		{
    			$cond = sprintf("%1\$s.page_id NOT IN ($p2c)", TBL_PAGE);
    		}
    		else
    		{	
    			$cond = sprintf("%1\$s.page_id IN ($p2c)", TBL_PAGE);
    		}
    	}
    	
    	$userCond = "";
    	if($userid != null)
    	{
    		if($cond != "")
    			$cond =  "(" . $cond . ") AND page_author = " . $userid;
    		else
    			$cond =  "page_author = " . $userid;
    	}
    	
    	$locale = $_SESSION["LC_ALL"];
    	
    	$sql = sprintf("SELECT * FROM %1\$s left join %2\$s " .
    			"on %1\$s.page_author = %2\$s.ID ".
				"LEFT JOIN %3\$s ON %1\$s.page_id = %3\$s.page_id AND %3\$s.lang_id = '$locale' ".
				"where %3\$s.lang_id='$locale' AND ($cond)", 
    			TBL_PAGE,
    			TBL_USER,
				TBL_PAGE_LANG);
    			
    	$res = $db->get_results($sql);
    	
    	return $res;
    }
    
    /**
     * Get all page with specified page_type
     * @return first page object if exist othewise return null;
     */
    function checkPagePositionExist($db, $type)
    {
    	$cond = " (page_type = {$type}) ";
    	
    	$arr = page::getAllPageByCondition($db, $cond);
    	
    	if(count($arr) == 0)
    	{
    		return null;
    	}
    	else
    	{
    		return $arr[0];
    	}
    }
    
    /**
     * Save the post information to Page
     * @param $page is $_POST param
     * Format:
     * [act] [page_name] [page_content] [submit] 
     * [page_cat] is Array ( [0] => 17 [1] => 18 ) and may not exist in 
     * 			  $page if user doesn't check category 
     * [page_status]
     * [page_parent] 
     * [page_author] 
     * @return: true if save successfully. Otherwise return false.
     */
    function savePage($db, $page)
    {
    	$res = $db->query(sprintf(
					"INSERT INTO %s" .
					"( page_status, page_parent,page_img, page_author," .
					"page_date, page_modified, quick_link, page_type) " .
					"VALUES('%s', '%d', '%s','%d'," .
					"now(), now(), '%d', '%s')",
    				TBL_PAGE, 
    				$page['page_status'],
    				$page['page_parent'],
					$page['page_img'],
    				$page['page_author'],
    				(isset($page['quicklink'])? $page['quicklink'] : '0'),
    				$page['page_type']));
		$id = $db->insert_id;	
		$country = $db->get_results("SELECT * FROM ".TBL_LANG);	
		if($country!=''){
			foreach($country as $item){			
				$db->query(sprintf(
							"INSERT INTO %s" .
							"(page_id,page_name,short_desciption, page_content, lang_id) " .
							"VALUES('%s', '%s', '%s', '%s', '%s')",
							TBL_PAGE_LANG, 
							$id,
							htmlentities($page['page_name'], ENT_QUOTES),
							htmlentities($page['short_desciption'], ENT_QUOTES),
							htmlentities($page['page_content'], ENT_QUOTES),
							$item->lang_key));
			}
		}				
    	if($res == true)
    	{
    		$newId = $id;
    		
    		//insert to page2cat
    		if(isset($page['page_cat']) && $page['page_cat'] != null)
    		{
    			foreach($page['page_cat'] as $cat)
    			{
    				$res = $db->query(sprintf(
								"INSERT INTO %s" .
									"(page_id, category_id) " .
									"VALUES(%d, %d)",
			    				TBL_PAGE2CAT, 
			    				$newId, $cat));
    			}
    		}
    		
    		return $newId;
    	}
    	else
    		return -1;
    }

	/**
	 * Delete the page.
	 * @param $id the id of page will be deleted.
	 * @return: true if save successfully. Otherwise return false.
	 */
	function deletePage($db, $id)
	{
		//Delete category
    	$sql = sprintf("DELETE FROM %1\$s" .
				" where %1\$s.page_id = '$id'", 
				TBL_PAGE);
		
    	$res = $db->query($sql);
    	
    	if($res > 0)
    	{
    		//Update pages that have page parent equal to this id
    		$sql = sprintf("UPDATE  %1\$s SET page_parent=0 WHERE page_parent = " . $id, 
					TBL_PAGE);
				
			$db->query($sql);	
    	
    		$sql = sprintf("DELETE FROM %1\$s" .
				" where %1\$s.page_id = $id", 
				TBL_PAGE2CAT);
		
    		$db->query($sql);
    	}
    	
    	return $res;
	}
	
	/**
	 * Get page by Id.
	 * @param $id the id of page will be deleted.
	 * @return page object, field name same as table in database.
	 */
	function getPageById($db, $id)
    {
    	$page = null;
    	$locale = $_SESSION["LC_ALL"];
    	$sql = sprintf("SELECT * FROM ".TBL_PAGE." p ".
						"LEFT JOIN ".TBL_PAGE_LANG." pl ON p.page_id = pl.page_id AND pl.lang_id = '$locale' ".	
						"WHERE p.page_id=" . $id."");
    	$page = $db->get_row($sql);
		if($page!=""){
					$page->page_name = html_entity_decode($page->page_name);
					$page->short_desciption = html_entity_decode($page->short_desciption);
					$page->page_content = html_entity_decode($page->page_content);
			}
    	return $page;
    }
	
	function getPageByType($db, $id)
    {
    	$page = null;
    	
    	$sql = sprintf("SELECT * FROM ".TBL_PAGE." p ". 
						"LEFT JOIN ".TBL_PAGE_LANG." pl ON p.page_id = pl.page_id AND pl.lang_id = '$locale' ".	
						"WHERE p.page_type=" . $id."");

    	$page = $db->get_row($sql);
		if($page!=""){
					$page->page_name = html_entity_decode($page->page_name);
					$page->short_desciption = html_entity_decode($page->short_desciption);
					$page->page_content = html_entity_decode($page->page_content);
			}
    	return $page;
    }
    
    /**
     * Get all category id in which page is related to.
     * @param $id the page id.
	 * @return list of category id.
	 */
	function getCategoryOfPage($db, $id)
    {
    	$cat = null;
    	
    	$locale = $_SESSION["LC_ALL"];
    	
    	$sql = sprintf("SELECT category_id FROM %1\$s WHERE %1\$s.page_id=" . $id, 
    			TBL_PAGE2CAT);
    	$cat = $db->get_results($sql);
    	return $cat;
    }
    
    /**
     * Update the page information.
     * @param $page the POST param send by HTML form.
     * @return true if execute query succesfully. Otherwise return false.
     */
    function updatePage($db, $page)
    {
		$locale = $_SESSION["LC_ALL"];
		if($page['page_img']!="")
			$cond = "page_img	= '".$page['page_img']."', ";
    	$res = $db->query("UPDATE " . TBL_PAGE . " SET " .
					"page_status 	= '$page[page_status]', " .
					"page_parent	= $page[page_parent], " .
					$cond.
					"page_author	= $page[page_author]," .
					"page_modified	= now(), " .
					"quick_link		= " . (isset($page['quicklink'])? $page['quicklink'] : '0') . "," .
					"page_type   	= '$page[page_type]'" .
					" WHERE page_id = " . $page['id']
    				);
     		$db->query("UPDATE " . TBL_PAGE_LANG . " SET " .
					"page_name 		= '".htmlentities($page['page_name'], ENT_QUOTES)."', " .
					"short_desciption 		= '".htmlentities($page['short_desciption'], ENT_QUOTES)."', " .
					"page_content 	= '". htmlentities($page['page_content'], ENT_QUOTES) . "'" .
					" WHERE page_id = " . $page['id'] ." AND lang_id = '" . $locale."'");
				
    	if($res == true)
    	{
    		$newId = $page['id'];
    		
    		$sql = sprintf("DELETE FROM %1\$s" .
				" where %1\$s.page_id = $newId", 
				TBL_PAGE2CAT);
		
    		$db->query($sql);
    		
    		//insert to page2cat
    		if(isset($page['page_cat']) && $page['page_cat'] != null)
    		{
    			foreach($page['page_cat'] as $cat)
    			{
    				$res = $db->query(sprintf(
								"INSERT INTO %s" .
									"(page_id, category_id) " .
									"VALUES(%d, %d)",
			    				TBL_PAGE2CAT, 
			    				$newId, $cat));
    			}
    		}
    		
    		return 0;
    	}
    	else
    		return -1;
    }
    
	function getPageByTypeFor_Home($db, $id,$langid)
    {
    	$page = null;
    	$locale = $_SESSION["LC_ALL"];
    	$sql = sprintf("SELECT p.*,pl.* FROM ".TBL_PAGE." p ".
						"LEFT JOIN ".TBL_PAGE_LANG." pl ON p.page_id = pl.page_id AND pl.lang_id = '$locale' ".	
						"WHERE p.page_type='$id'" );
    	$page = $db->get_row($sql);
		if($page!=""){
					$page->page_name = html_entity_decode($page->page_name);
					$page->short_desciption = html_entity_decode($page->short_desciption);
					$page->page_content = html_entity_decode($page->page_content);
			}
    	return $page;
    }
	
}
?>